package com.ericsson.resourceplanner.service.impl;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.ericsson.resourceplanner.dao.AdminDao;
import com.ericsson.resourceplanner.dao.entity.EmployeeProjectDailyBackup;
import com.ericsson.resourceplanner.dao.entity.EriproDumpDetail;
import com.ericsson.resourceplanner.dao.entity.Report;
import com.ericsson.resourceplanner.dto.EriproDto;
import com.ericsson.resourceplanner.exceptions.ResourcePlannerException;
import com.ericsson.resourceplanner.service.AdminServ;
import com.ericsson.resourceplanner.utils.AdminUtility;
import com.ericsson.resourceplanner.utils.CommonUtils;
import com.ericsson.resourceplanner.utils.ExcelLayoutManager;
import com.ericsson.resourceplanner.utils.ResourcePlannerConstants;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Service
public class AdminServImpl implements AdminServ{
	@Autowired
	ExcelLayoutManager layoutManager;
	@Autowired
	AdminUtility adminUtility;
	
	@Autowired
	AdminDao adminDao;
	
	public void setAdminUtility(AdminUtility adminUtility) {
		this.adminUtility = adminUtility;
	}


	public void setAdminDao(AdminDao adminDao) {
		this.adminDao = adminDao;
	}

	protected Logger logger = LoggerFactory.getLogger(getClass());
	final int BATCH_SIZE = 50;
	
	@Override
	public void uploadFileService(MultipartFile file, String name)
	{
		try
		{
			Workbook wb = null;
			//OPCPackage opcPackage = OPCPackage.openOrCreate(file);
			//XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

			
			System.out.println("name ="+name);
			String file_extn = name.substring(name.lastIndexOf(".")+1);
			if(file_extn.equals("xlsx"))
			{
				wb = new XSSFWorkbook(file.getInputStream());
				
			}
			if(file_extn.equals("xls"))
			{
				wb = new HSSFWorkbook(file.getInputStream());
			}
			Sheet sheet = wb.getSheetAt(0);
			Iterator<Row> rowIterator = sheet.iterator();
			
			Cell signum = null, empname = null, domain = null, region = null, 
					projectid = null, projname = null, customer = null, fte = null, 
					status = null, start_date = null, end_date = null;
					
			String signum_id = " ", emp_name, domain_name=" ", region_code, proj_name=" ", customer_name=" ", curr_status=" ";
			Date startdate, enddate;
			double proj_id=00000000, fte_hrs= 0.0;
			
			EriproDto edto = null;
			
			Set<EriproDto> edtoset = new HashSet<EriproDto>();
			int counter = 0;
			boolean invalidflag = false;
			while(rowIterator.hasNext())
			{
				Row row = rowIterator.next();
				invalidflag = false;
				if(row.getRowNum() != 0)
				{
					signum = row.getCell(6);
					empname = row.getCell(36);
					domain = row.getCell(3);
					region = row.getCell(50);
					projectid = row.getCell(20);
					fte = row.getCell(5);
					start_date = row.getCell(16);
					end_date = row.getCell(17);
					
					
					if(signum==null || empname == null || domain == null || region == null || projectid == null 
							|| fte == null || start_date == null || end_date == null)
					{
						invalidflag = true;
					}
					else
					{
						signum_id = adminUtility.getCellValue(signum);//signum.getStringCellValue();
						
						emp_name = adminUtility.getCellValue(empname);//empname.getStringCellValue();
						region_code = adminUtility.getCellValue(region);//region.getStringCellValue();
						domain_name = adminUtility.getCellValue(domain);
						//customer_name = adminUtility.getCellValue(customer);
						//curr_status = adminUtility.getCellValue(status);
						startdate = start_date.getDateCellValue();
						enddate = end_date.getDateCellValue();
						if(!fte.equals(null))
						{
							fte_hrs = Double.parseDouble(adminUtility.getCellValue(fte));//fte.getNumericCellValue();
						}
						if(!projectid.equals(null))
						{
							proj_id = Double.parseDouble(adminUtility.getCellValue(projectid));//projectid.getNumericCellValue();
						}
						
						//System.out.println("signum------------------------------------------------------------------------------------"+signum_id);
						//System.out.println("emp_name------------------------------------------------------------------------------------"+emp_name);
						edto = new EriproDto();
						edto.setCurr_status(curr_status);
						edto.setCustomer_name(customer_name);
						edto.setDomain_name(domain_name);
						edto.setEmp_name(emp_name);
						edto.setEnddate(enddate);
						edto.setFte_hrs(fte_hrs);
						edto.setProj_id(proj_id);
						edto.setProj_name(proj_name);
						edto.setRegion_code(region_code);
						edto.setSignum_id(signum_id);
						edto.setStartdate(startdate);
						
						//adminDao.uploadFileInDB(edto);
						
						if((counter<BATCH_SIZE && rowIterator.hasNext()))
						{
							//System.out.println("counter within if--------------------------"+counter);
							//System.out.println(counter+" object ------------------------------------ empname===="+edto.getEmp_name());
							//System.out.println("------------------------------------ signum===="+edto.getSignum_id());
							edtoset.add(edto);
							counter++;
						}
						else
						{  /*for(EriproDto eriproDto:edtoset){
							System.out.println("eriproDto:"+eriproDto.getEmp_name()+":"+eriproDto.getSignum_id());
						}*/
							System.out.println("counter in the else part ========================================================== "+counter);
							System.out.println("set size formed in serv impl = "+edtoset.size());
							adminDao.uploadFileInDB(edtoset);
							counter = 0;
							edtoset.clear();
						}
					}//invalidflag check end					
				}
			}//while end
			
			//entityManager.clear();
			//entityManager.close();
			adminDao.insertProjRecords();
			logger.debug("insert into project table is done here----------------------------------");
			adminDao.insertEmpProjRecords();
			logger.debug("insert into employee_project table is done here----------------------------------");
	   }
	   catch(IOException io)
	   {
		   io.printStackTrace();
	   }
	}
	
	public Workbook getExportDataFromDataBase(String minDate, String maxDate){
		Workbook workbook=null;
		List<Report>resultsList=null;
		Date startDate=CommonUtils.getFormattedDateFromString("dd-MMM-yyyy", minDate);
		Date endDate = CommonUtils.getFormattedDateFromString("dd-MMM-yyyy", maxDate);
		String dateRange=minDate + ResourcePlannerConstants.HYPHEN + maxDate;
		//Gets the list of all the result from the Database
		resultsList=adminDao.getExportResultsFromDB(startDate, endDate);
		if(resultsList==null || resultsList.size()==0){
			throw new ResourcePlannerException("Received No records from Database");
		}else{
			workbook=createExcel(dateRange, resultsList);
		}

		return workbook;

	}

	@SuppressWarnings("resource")
	public  final Workbook createExcel(String sheetName, List<Report> dataMap) throws ResourcePlannerException{
		Workbook workbook = null;
		workbook= new HSSFWorkbook();
		workbook.createSheet(sheetName);
		workbook=prepareAndFillExcel(workbook, dataMap, null);

		return workbook;
	}

	public void closeWorkbook(Workbook workbook){
		if(workbook !=null){
			try {
				workbook.close();
			} catch (IOException e) {
				throw new ResourcePlannerException("Exception While Closing Workbook", e);

			}
		}
	}

	private Workbook prepareAndFillExcel(Workbook workbook, List<Report> resultFromDB,ArrayList<String> parameterNames)
			throws ResourcePlannerException{
		
		boolean isHeaderCreated=false;
		Sheet sheet=workbook.getSheetAt(0);
		CellStyle HeaderStyle= ExcelLayoutManager.buildHeaderStyle(sheet);
		CellStyle cellStyle=ExcelLayoutManager.buildDataCellStyle(sheet);
		
		for(int rowNum=0,index=0;index <resultFromDB.size();rowNum++,index++){

			Row row=sheet.createRow(rowNum);
			Report currentResultObject=resultFromDB.get(index);
			Map<String, Object>currentDataMap=CommonUtils.ConvertObjectToMap(currentResultObject);
			Set<String>keys=currentDataMap.keySet();
			if(!isHeaderCreated){
				row.setHeight((short)500);
				sheet.createFreezePane( 0, 1, 0, 1 );
				int i=0;
				for(String key:keys){

					Cell cell=row.createCell(i);
					cell.setCellValue(key);
					cell.setCellStyle(HeaderStyle);
					sheet.autoSizeColumn(i);
					i++;
					isHeaderCreated=true;
					
					
				}
				fillDataCells(currentDataMap, sheet.createRow(++rowNum), keys, sheet,cellStyle);
			}else{
				fillDataCells(currentDataMap, row, keys, sheet,cellStyle);
			}

		}

		return workbook;
	}
	
	private void fillDataCells(Map<String, Object> currentDataMap,Row row,Set<String> keys,Sheet sheet, CellStyle cellStyle){
		int columnNo=0;
		for(String key:keys){
			Cell cell=row.createCell(columnNo);
			cell.setCellValue((currentDataMap.get(key)).toString());
			cell.setCellStyle(cellStyle);
			columnNo++;
		}
	}
	
	public final Workbook callCreateExcel(String name,List<Report>dataMap){
		Workbook workbook= createExcel(name, dataMap);
		return workbook;
	} 
	
	@Override
	public Workbook callExportMonthlyReport(String month, String year){
		Date minDate=null;
		Date maxDate=null;
		List<Report>resultsList=null;
		Workbook workbook=null;
		String dateRange=month+ResourcePlannerConstants.UNDERSCORE+year;
		Calendar calendar= Calendar.getInstance();
		calendar.set(Calendar.YEAR, Integer.parseInt(year));
		calendar.set(Calendar.MONTH, Integer.parseInt(month));
		calendar.set(Calendar.DAY_OF_MONTH,0);
		
		minDate=new Date();
		minDate.setTime(calendar.getTimeInMillis());
		
		int maxDay=calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
		calendar.set(Calendar.DAY_OF_MONTH, maxDay);
		maxDate= new Date();
		maxDate.setTime(calendar.getTimeInMillis());
		resultsList= adminDao.getExportResultsFromDB(minDate, maxDate);
		if(resultsList==null || resultsList.size()==0){
			throw new ResourcePlannerException("Received No records from Database");
		}else{
			workbook=createExcel(dateRange, resultsList);
		}

		return workbook;

		
	}
	
	
	@Override
	public void fileupload(MultipartFile file, String name) {

		try {

			Workbook wb = null;

			String file_ext = name.substring(name.lastIndexOf(".") + 1,
					name.length());

			System.out.println("File extension ::" + file_ext);

			if (file_ext.equalsIgnoreCase("xlsx")) {
				System.out.println("in if");
				wb = new XSSFWorkbook(file.getInputStream());
			}

			else if (file_ext.equalsIgnoreCase("xls")) {
				wb = new HSSFWorkbook(file.getInputStream());
			}

			Sheet s = wb.getSheetAt(1);
			Iterator<Row> rowiterator = s.iterator();

			Cell network = null;
			Cell date = null;
			Cell hours = null;
			Cell signum = null;

			BigDecimal network_val;
			Date date_val;
			BigDecimal hours_val;
			String signum_val;
			Row r;
			EmployeeProjectDailyBackup obj;

			Calendar currentDateBefore2Months = Calendar.getInstance();
			currentDateBefore2Months.add(Calendar.MONTH, -2);

			String batch_id = adminDao.getbatch_id();

			while (rowiterator.hasNext()) {

				r = rowiterator.next();

				System.out.println("Row Number ::" + r.getRowNum());

				if (r.getRowNum() != 0) {
					network = r.getCell(4);
					date = r.getCell(11);
					hours = r.getCell(12);
					signum = r.getCell(19);

					date_val = date.getDateCellValue();
					if (date_val.after(currentDateBefore2Months.getTime())) {
						network_val = new BigDecimal(adminUtility.getCellValue(network));

						hours_val = new BigDecimal(adminUtility.getCellValue(hours));
						signum_val = adminUtility.getCellValue(signum);

						System.out.println("network_val ::" + network_val
								+ "date_val ::" + date_val + "hours_val ::"
								+ hours_val + "signum_val ::" + signum_val);

						obj = new EmployeeProjectDailyBackup();
						obj.setProjectId(network_val);
						obj.setWorkingDate(date_val);
						obj.setProdHours(hours_val);
						obj.setSignumId(signum_val);
						obj.setBatch_id(batch_id);

						adminDao.saveprojdailyutilization(obj);

					}
				}
			}

			if (!adminDao.getbatch_id().equalsIgnoreCase(batch_id))
				adminDao.populate_week_data();

		} catch (IOException e) {
			e.printStackTrace();
		}

	}

}
